package net.kesumu.yushabuserver.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;

import net.kesumu.yushabuserver.dao.IYushabuSessionDao;
import net.kesumu.yushabuserver.entity.User;
import net.kesumu.yushabuserver.entity.YushabuSession;
import net.kesumu.yushabuserver.utils.DataSource;

public class YushabuSessionDaoImpl implements IYushabuSessionDao{

    public YushabuSessionDaoImpl() {
    }

    @Override
    public YushabuSession setupSession(YushabuSession originalSession) {
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        String insertSQL = 
                "INSERT INTO " + DataSource.dbName + ".session "
                + "(type, createdTime, name, createUserId) "
                + "VALUES"
                + "(" 
                + "'" + originalSession.getType() + "', " 
                + "'" + originalSession.getCreatedTime().toString() + "', " 
                + "'" + originalSession.getName() + "', " 
                + "'" + originalSession.getCreateUserId() + "'" 
                + ")";
        try {
            PreparedStatement insertStatement_Session = conn.prepareStatement(insertSQL
                                                                , Statement.RETURN_GENERATED_KEYS);
            int affectedRows = insertStatement_Session.executeUpdate();
            if (affectedRows == 0) {
                throw new SQLException("Creating user failed, no rows affected.");
            }
            try (ResultSet generatedKeys = insertStatement_Session.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    //get the sessionId just inserted
                    originalSession.setSessionId(generatedKeys.getInt(1));
                }
                else {
                    throw new SQLException("Creating user failed, no ID obtained.");
                }
            }
        }catch(SQLException e) {
            e.printStackTrace();
            return null;
        }
        return originalSession;
    }

    @Override
    public YushabuSession getSessionBySessionId(int yushabuSessionId) {
        YushabuSession yushabuSession = new YushabuSession();
        
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        String selectSQL = 
                "SELECT * FROM " + DataSource.dbName + ".session "
                + "WHERE sessionId='" + yushabuSessionId + "'";
        ResultSet resultSet = null;
        try {
            resultSet = conn.prepareStatement(selectSQL).executeQuery();
            if (resultSet.next()) {
                yushabuSession.setSessionId(resultSet.getInt(1));
                yushabuSession.setType(resultSet.getInt(2));
                yushabuSession.setCreatedTime(Timestamp.valueOf(resultSet.getString(3)));
                yushabuSession.setName(resultSet.getString(4));
                yushabuSession.setCreateUserId(resultSet.getInt(5));
            }
        }catch(SQLException e) {
            e.printStackTrace();
            return null;
        }
        return yushabuSession;
    }

    @Override
    public ArrayList<User> getSessionMemberIdsBySessionId(int yushabuSessionId) {
        ArrayList<User> sessionMembers = new ArrayList<User>();
        
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        String selectSQL = 
                "SELECT * FROM " + DataSource.dbName + ".sessionmember "
                + "WHERE sessionId='" + yushabuSessionId + "'";
        ResultSet resultSet = null;
        try {
            resultSet = conn.prepareStatement(selectSQL).executeQuery();
            while (resultSet.next()) {
                User sessionMember = new User();
                sessionMember.setUserId(resultSet.getInt(2));
                sessionMembers.add(sessionMember);
            }
        }catch(SQLException e) {
            e.printStackTrace();
            return null;
        }
        return sessionMembers;
    }
    
    @Override
    public boolean removeSessionMember(int sessionId, int userId) {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public boolean addSessionMember(int sessionId, int userId) {
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        String insertSQL = 
                "INSERT INTO " + DataSource.dbName + ".sessionmember "
                + "(userId, sessionId) "
                + "VALUES"
                + "(" 
                + "'" + userId + "', " 
                + "'" + sessionId + "'" 
                + ")";
        try {
            conn.prepareStatement(insertSQL).executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ds.close();
        }
        return true;
    }


}
